﻿Imports System.Data.SqlClient
Imports CrystalDecisions.CrystalReports.Engine

Public Class frmDeductibles

    Sub FillStaff()
        Try
            Me.Cursor = Cursors.Default

            Dim cmd As New SqlCommand("select EmpNo,FullNameEn from StaffProfiles", cnn)
            Dim Reader As SqlDataReader

            Me.ListUsers.Items.Clear()
            Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                Dim Item As New ListViewItem
                Item.Text = Reader.Item("EmpNo")
                Item.SubItems.Add(Reader.Item("FullNameEn"))
                Me.ListUsers.Items.Add(Item)
            End While
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub Clear()
        Me.txtName.Clear()
        Me.txtJobTitle.Clear()
        Me.txtBranch.Clear()

        Me.txtNotes.Clear()
        Me.txtAmount.Value = 0
        Me.txtNotes.Clear()
        Me.GridDeductionsHistory.Rows.Clear()
    End Sub

    Sub ClearSub()
        Me.txtNotes.Clear()
        Me.txtAmount.Value = 0
    End Sub

    Private Sub butClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles butClear.Click
        ClearSub()
    End Sub

    Private Sub butClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles butClose.Click
        Me.Close()
    End Sub

    Sub FillDeductionsHistory()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand(" Select * From PayrollDeductible " & _
                                      "Where Item<>N'Loans' and StaffID=" & CInt(Me.ListUsers.SelectedItems(0).Text) & _
                                      " Order By Year,Month", cnn)
            Dim Reader As SqlDataReader

            Me.GridDeductionsHistory.Rows.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            With Me.GridDeductionsHistory
                While Reader.Read
                    Me.GridDeductionsHistory.Rows.Add({Reader.Item("SNo"), Reader.Item("Item"), _
                                                       Reader.Item("Year"), MonthName(CInt(Reader.Item("Month")), False), _
                                                       CDbl(Reader.Item("Amount")).ToString("N2"), Reader.Item("Notes"), _
                                                       Reader.Item("CreatedBy"), CDate(Reader.Item("CreatedOn")).ToString("dd-MMM-yyyy")})
                End While
            End With
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub butSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles butSave.Click
        Me.ErrProv.Clear()
        If Me.TxtName.Text.Trim.Length = 0 Then
            MsgBox("Please select a staff member from the list")

        ElseIf Me.txtAmount.Value = 0 Then
            Me.ErrProv.SetError(Me.txtAmount, "Please fill in all details")
            Me.txtAmount.Focus()

        ElseIf Me.txtNotes.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtNotes, "Please fill in all details")
            Me.txtNotes.Focus()

        Else
            Try
                Me.Cursor = Cursors.WaitCursor

                Dim Reason As String
                If Me.RAbsence.Checked = True Then
                    Reason = "Absence"

                ElseIf Me.RDelays.Checked = True Then
                    Reason = "Delays"

                ElseIf Me.RPenalties.Checked = True Then
                    Reason = "Penalties"
                End If

                Dim Trans As SqlTransaction
                Dim cmd As New SqlCommand("Insert Into PayrollDeductible " & _
                                          "(StaffID,Year,Month,Item,Notes,Amount,CreatedBy) " & _
                                          "Values " & _
                                          "(@StaffID,@Year,@Month,@Item,@Notes,@Amount,@CreatedBy)", cnn)

                cnn.Open()
                Trans = cnn.BeginTransaction
                cmd.Connection = cnn
                cmd.Transaction = Trans

                cmd.Parameters.Clear()
                cmd.Parameters.AddWithValue("@StaffID", Me.txtEmpNo.Text)
                cmd.Parameters.AddWithValue("@Year", Me.DTPInstallmentMonth.Value.Year)
                cmd.Parameters.AddWithValue("@Month", Me.DTPInstallmentMonth.Value.Month)
                cmd.Parameters.AddWithValue("@Item", Reason)
                cmd.Parameters.AddWithValue("@Notes", Me.txtNotes.Text.Trim)
                cmd.Parameters.AddWithValue("@Amount", Me.txtAmount.Value)
                cmd.Parameters.AddWithValue("@CreatedBy", CurrentUser)
                cmd.ExecuteNonQuery()

                Trans.Commit()
                cnn.Close()

                MsgBox("Saved Successfully!")

                ClearSub()

                FillDeductionsHistory()

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Private Sub btnPrint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrint.Click
        If txtName.Text.Trim.Length = 0 Then
            MsgBox("Please enter a valid staff ID!")
            Exit Sub
        Else
            Try
                Me.Cursor = Cursors.WaitCursor

                Dim dap As New SqlDataAdapter("Select * From StaffLeave Where EmpID=" & Me.txtEmpNo.Text, cnn)
                Dim das As New DsVacations

                dap.Fill(das, "StaffLeave")

                Dim rpt As New StaffLeaveStatement
                rpt.SetDataSource(das)
                rptViewer.CrystalReportViewer1.ReportSource = rpt
                rptViewer.ShowDialog()

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Private Sub frmStaffIncidents_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        FillStaff()

        Me.WindowState = FormWindowState.Maximized
    End Sub

    Sub FillUsers()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select EmpNo,FullNameEn From StaffProfiles", cnn)
            Dim Reader As SqlDataReader

            Me.ListUsers.Items.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                Dim Item As New ListViewItem
                Item.Text = Reader.Item("EmpNo")
                Item.SubItems.Add(Reader.Item("FullNameEn"))
                Me.ListUsers.Items.Add(Item)
            End While
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            MsgBox(ex.ToString)
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
        End Try
    End Sub

    Private Sub ListUsers_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListUsers.SelectedIndexChanged
        Clear()

        If Me.ListUsers.SelectedItems.Count > 0 Then
            Try
                Me.Cursor = Cursors.WaitCursor
                Dim cmd As New SqlCommand("Select EmpNo,FullNameEn,Position,Branch,VacationPeriod " & _
                                          "From StaffProfiles where EmpNo=" & CInt(Me.ListUsers.SelectedItems(0).Text), cnn)
                Dim Reader As SqlDataReader

                cnn.Open()
                Reader = cmd.ExecuteReader
                While Reader.Read
                    Me.txtEmpNo.Text = Reader.Item("EmpNo")
                    Me.txtName.Text = Reader.Item("FullNameEn")
                    Me.txtBranch.Text = Reader.Item("Branch")
                End While
                Reader.Close()
                cnn.Close()

                FillDeductionsHistory()

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Private Sub GridDeductionsHistory_CellClick(sender As System.Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridDeductionsHistory.CellClick
        If e.ColumnIndex = Me.DGColDelete.Index Then
            If MsgBox("Confirm delete?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
                Try
                    Me.Cursor = Cursors.WaitCursor

                    Dim cmd As New SqlCommand("Delete From PayrollDeductible Where SNo=" & _
                                              CInt(Me.GridDeductionsHistory.CurrentRow.Cells(Me.DGColSNo.Index).Value), cnn)

                    cnn.Open()
                    cmd.ExecuteNonQuery()
                    cnn.Close()

                    FillDeductionsHistory()

                    Me.Cursor = Cursors.Default
                Catch ex As Exception
                    Me.Cursor = Cursors.Default
                    If cnn.State = ConnectionState.Open Then
                        cnn.Close()
                    End If
                    MsgBox(ex.ToString)
                End Try
            End If
        End If
    End Sub
End Class